﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EyFramework.ORM;
using ShuJuGj.Entity;
using ShuJuGj.Entity.Dto;

namespace ShuJuGj.Core.Dao
{
    /// <summary>
    ///  TJ_TJBG_PDF表的数据库操作
    /// </summary>
    public class TjTjbgPdfDao : BaseDaoTj<TjTjbgPdf>, ITjTjbgPdfDao
    {
        public TjTjbgjl GetTjbgjl(string tjbh)
        {
            string sql = @"select A.TJBH TIJIANBH,b.xm as XINGMING,
                (case b.xb when 1 then '男' when 2 then '女' when 0 then '未知' else '未知' end) as XINGBIE,
            convert(varchar(8), b.csny, 112) as CHUSHENRQ,
            UPPER(b.SFZH) as SHENFENZH,
            (select ygxm from tj_ygdm where yggh = a.zjys) as ZHONGJIANYS,
            convert(varchar(100), a.tjrq, 20) as TIJIANRQ,
            b.LXDH as LIANXIDH,
            (SELECT MC FROM TJ_DWDMB WHERE DWBH = a.DWBH) AS DANWEIMC,
                a.ZJRQ ZHONGJIANRQ,
            (SELECT convert(varchar(100), SCRQ, 20) FROM TJ_TJBG_PDF WHERE TJBH = A.TJBH) SHANGCHUANRQ
                from TJ_TJDJB a, TJ_TJDAB b
                where a.DABH = b.DABH and a.SUMOVER = '1'
            and isnull(b.SFZH,'')<> '' AND A.TJBH='{0}' ";
            return Db.Ado.SqlQuery<TjTjbgjl>(string.Format(sql, tjbh)).First();
        }

        public TjTjbgPdf GetTjbgPdf(string tjbh)
        {
            return Db.Queryable<TjTjbgPdf>().Where(p => p.TJBH == tjbh).First();
        }

        public List<TjTjbgjl> GetTjbgPdfList(TjbgPdfParam queryParam)
        {
            string sql = @"select A.TJBH TIJIANBH,b.xm as XINGMING,
                (case b.xb when 1 then '男' when 2 then '女' when 0 then '未知' else '未知' end) as XINGBIE,
            convert(varchar(8), b.csny, 112) as CHUSHENRQ,
            UPPER(b.SFZH) as SHENFENZH,
            (select ygxm from tj_ygdm where yggh = a.zjys) as ZHONGJIANYS,
            convert(varchar(100), a.tjrq, 20) as TIJIANRQ,
            b.LXDH as LIANXIDH,
            (SELECT MC FROM TJ_DWDMB WHERE DWBH = a.DWBH) AS DANWEIMC,
                a.ZJRQ ZHONGJIANRQ,
            (SELECT convert(varchar(100), SCRQ, 20) FROM TJ_TJBG_PDF WHERE TJBH = A.TJBH) SHANGCHUANRQ
                from TJ_TJDJB a, TJ_TJDAB b
                where a.DABH = b.DABH and a.SUMOVER = '1'
            and isnull(b.SFZH,'')<> ''";
            if (!string.IsNullOrEmpty(queryParam.START_DT))
            {
                sql = sql + string.Format(" and a.zjrq >= CONVERT(datetime, '{0} 00:00:00', 20) ", queryParam.START_DT);
            }

            if (!string.IsNullOrEmpty(queryParam.END_DT))
            {
                sql = sql + string.Format(" and a.zjrq <= CONVERT(datetime, '{0} 23:59:59', 20) ", queryParam.END_DT);
            }

            if (!string.IsNullOrEmpty(queryParam.TIJIANBH))
            {
                sql = sql + string.Format(" and A.TJBH='{0}'", queryParam.TIJIANBH);
            }

            if (!string.IsNullOrEmpty(queryParam.XINGMING))
            {
                sql = sql + string.Format(" and b.xm like '{0}%'", queryParam.XINGMING);
            }

            if (queryParam.UPLOAD == 1)
            {
                sql = sql + " AND EXISTS(SELECT 1 FROM TJ_TJBG_PDF WHERE TJBH=A.TJBH) ";
            }
            else if (queryParam.UPLOAD == 2)
            {
                sql = sql + " AND NOT EXISTS(SELECT 1 FROM TJ_TJBG_PDF WHERE TJBH=A.TJBH) ";
            }

            sql = sql + "order by a.zjrq desc";
            return Db.Ado.SqlQuery<TjTjbgjl>(sql);
        }

        public List<string> GetUnloadData()
        {
            string sql =
                "SELECT WS01_00_018_01 FROM SJGJ_JKTJBGJL A WHERE NOT EXISTS(SELECT 1 FROM TJ_TJBG_PDF WHERE TJBH=A.WS01_00_018_01 AND SCBZ=1)";
            return Db.Ado.SqlQuery<string>(sql).ToList();
        }

        public TjTjbgPdf GetUploadTjbgPdf(string tjbh)
        {
            return Db.Queryable<TjTjbgPdf>().Where(p => p.TJBH == tjbh).First();
        }

        public bool UpdateScbz(TjTjbgPdf tjbgPdf)
        {
            return Db.Updateable(tjbgPdf).UpdateColumns(p => p.SCBZ == tjbgPdf.SCBZ).ExecuteCommand() >= 0;
        }
    }
}